IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_VisitsByWeekAndContact')
	BEGIN
		DROP  Procedure  up_Report_VisitsByWeekAndContact
	END

GO

CREATE Procedure up_Report_VisitsByWeekAndContact
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SET @ProjectId = 8
END

SELECT     
	VC.Name AS Person, 
	DATEPART(wk, V.SatelliteTime) AS weekNumber, 
	1 AS numberVisit, 
	convert(nvarchar,dbo.fn_GetFirstDateofWeek(V.SatelliteTime),101) as FirstWeekDate,
	convert(nvarchar,dbo.fn_GetLastDateofWeek(V.SatelliteTime),101) as LastWeekDate,
	(select
		count(P.Vendor_Visit_XMissionStructureFK) 
			from tb_Vendor_Visit_XMissionStructures_Poles P 
			where P.Vendor_Visit_XMissionStructureFK = V.Vendor_Visit_XMissionStructureId
		) as CountPoles
FROM         
	tb_Vendor_Visit_XMissionStructures AS V 
	INNER JOIN tb_Vendor_Contacts AS VC ON V.Vendor_ContactFK = VC.Vendor_ContactId
	
WHERE Vendor_ProjectFK = @ProjectId
ORDER BY DATEPART(wk, V.SatelliteTime)



GO

GRANT EXEC ON up_Report_VisitsByWeekAndContact TO PPIReports

GO

